﻿using System;
using System.Data;
using System.Configuration;
 
using System.Data.SqlClient;
using System.Text;

namespace Code
{
    public class ClsCon
    {
        public SqlConnection conn;
        //SqlConnection Con;
        public SqlConnection SqlConn;
        private int intCount = 0;
        /// <summary>
        /// Connection method
        /// </summary>
        public SqlConnection ConDatabase()
        {
            string str = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; 
            //string str = ConfigurationManager.ConnectionStrings["SqlConnection"].ToString();
            conn = new SqlConnection(str);
            conn.Open();
            return conn;
        }
        /// <summary>
        /// close Connection method
        /// </summary>
        /// <returns></returns>
        public bool closeCon()
        {
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();

                }
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 执行SQL语句，返回string
        /// </summary>
        /// <param name=""></param>
        public string strReadBySQL(String strPrmSQL)
        {

            StringBuilder commandText = new StringBuilder(strPrmSQL);
            try
            {
                conn = DBconn();
                conn.Open();
                SqlCommand DSCmd = new SqlCommand(commandText.ToString(), conn);
                SqlTransaction trans = conn.BeginTransaction();
                DSCmd.Transaction = trans;

                return Convert.ToString(DSCmd.ExecuteScalar());
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet ExceDS(string cmdtxt, string tblName)
        {
            SqlConnection Con = ConDatabase();
            SqlCommand Com;
            DataSet ds = null;
            try
            {
                Com = new SqlCommand(cmdtxt, Con);
                SqlDataAdapter Da = new SqlDataAdapter();
                Da.SelectCommand = Com;
                ds = new DataSet(tblName);
                Da.Fill(ds);
            }
            catch (Exception ex)
            {
                Con.Close();
            }
            return ds;
        }
        public SqlConnection DBconn()
        {
            string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; 
          //  string strConn = ConfigurationManager.ConnectionStrings["SqlConnection"].ToString();
            try
            {
                return new SqlConnection(strConn);
            }
            catch (Exception)
            {
                return null;
            }


        }
        public bool udiDataBase(string UDIString)
        {
            string strError = "";
            SqlConnection conn;
            if (SqlConn == null)
            {
                conn = DBconn();
            }
            else
            {
                conn = SqlConn;
            }
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();


                }
                SqlCommand cmd = new SqlCommand(UDIString, conn);
                cmd.CommandTimeout = 0;//由300--0,2011.2.27
                cmd.CommandType = CommandType.Text;
                intCount = cmd.ExecuteNonQuery();
                return !(intCount < 1);
            }
            catch (Exception e)
            {
                strError = "更新数据库失败：" + e.Message;
                return false;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        public bool Insert(string InsertString)
        {
            return udiDataBase(InsertString);
        }


        /// <summary>
        /// 返回一个DataSet数据类型的数据
        /// </summary>
        /// <param name="cmdtxt">要执行的SQL语句</param>
        /// <param name="tblName">要绑定的数据表</param>
        /// <returns></returns>
        public DataSet CreatDaSet(string cmdtxt)
        {

            SqlConnection conn;
            if (SqlConn == null)
            {
                conn = DBconn();
            }
            else
            {
                conn = SqlConn;
            }
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                SqlDataAdapter Adpt = new SqlDataAdapter(cmdtxt, DBconn());
                DataSet Ds = new DataSet();
                Adpt.Fill(Ds);
                return Ds;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                    conn.Dispose();
                }

            }



        }

        public DataTable CreatDaTable(string cmdtxt)
        {

            SqlConnection conn;
            if (SqlConn == null)
            {
                conn = DBconn();
            }
            else
            {
                conn = SqlConn;
            }
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                SqlDataAdapter Adpt = new SqlDataAdapter(cmdtxt, DBconn());
                DataTable Ds = new DataTable();
                Adpt.Fill(Ds);
                return Ds;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                    conn.Dispose();
                }

            }



        }
        ///取一个字段的数值
        public string GetFieldValue(string cmdtxt)
        {
            string tvalue = "";
            SqlConnection conn;
            if (SqlConn == null)
            {
                conn = DBconn();
            }
            else
            {
                conn = SqlConn;
            }
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                SqlDataAdapter Adpt = new SqlDataAdapter(cmdtxt, DBconn());
                DataSet Ds = new DataSet();
                Adpt.Fill(Ds);
                if (Ds.Tables[0].Rows.Count > 0)
                {
                    tvalue = Ds.Tables[0].Rows[0][0].ToString();
                }
                return tvalue;
            }


            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                    conn.Dispose();
                }

            }



        }

        public bool Delete(string DelString)
        {
            return udiDataBase(DelString);
        }

       
    }
}

